# !/usr/bin/env python
# encoding: utf-8
'''
  @author: HJW
  @license: (C) Copyright 2013-2017, Node Supply Chain Manager Corporation Limited.
  @contact: hejunwang01@126.com
  @file: operation_excel.py
  @time: 2024/4/14 0014 上午 10:42
  @desc:
  '''
import os.path

from openpyxl import *

import logging

logger = logging.getLogger(__name__)


class ExcelOp(object):
    def __init__(self, filepath:str):
        logger.info(f'当前要操作的 文件opeartion file :{filepath}')
        self.filepath = filepath
        # 获取工作簿
        self.wb = load_workbook(self.filepath)
        # 获取工作表sheet
        sheets = self.wb.sheetnames
        self.sheet = sheets[0]
        logger.info(f"处理的是的第一个sheet:{self.sheet}")
        self.ws = self.wb[self.sheet]

    # 获取表格的有效的总行数
    def get_row_clo_num(self):
        i = self.ws.max_row
        real_max_row = 0
        while i > 0:
            row_dict = {i.value for i in self.ws[i]}
            if row_dict == {None}:
                i = i - 1
            else:
                real_max_row = i
                break

        return real_max_row

    # 获取所有行的所有值
    def get_row_col_value(self):
        rows = self.get_row_clo_num()
        column = self.ws.max_column
        row_column_data = []
        # 读取每行的数据
        for row in range(2, rows + 1):
            row_data = []
            for col in range(1, column + 1):
                cell_value = self.ws.cell(row=row, column=col).value
                # logger.info(f"当前是row:{row} ,col是:{col},值是:{cell_value}")
                row_data.append(cell_value)
            row_column_data.append(row_data)
        logger.info(f"获取所有的value:{row_column_data}")

        return row_column_data

    # 写入excel表格中，指定的位置写入指定的数据
    def write_excel_value(self, row, col, param):
        self.ws.cell(row=row, column=col, value=str(param))
        self.wb.save(self.filepath)
        logger.info(f"写入行row:{row},column列:{col}数据:{param} 写入成功！")

    def delete_excel_col_value(self, col=None):
        # 指定要删除的Lie号
        column_index = col
        rows = self.get_row_clo_num()
        # 删除从第二行开始的col列的数据
        logger.info(rows)
        for row in range(2, rows + 1):
            for it in col:
                logger.info(row, it)
                self.ws.cell(row=row, column=it).value = None

        self.wb.save(self.filepath)
        logger.info(f"删除列{column_index}数据成功...")

    def delete_excel_col_value_list(self, col_list=None):
        # 指定要删除的Lie号
        column_index = col_list
        rows = self.get_row_clo_num()
        # 删除从第二行开始的col列的数据
        logger.info(rows)
        for row in range(2, rows + 1):
            for it in col_list:
                logger.info(row, it)
                self.ws.cell(row=row, column=it).value = None

        self.wb.save(self.filepath)
        logger.info(f"删除列{col_list}数据成功...")

    def write_to_excel(self, data):

        for row_index, row_item in enumerate(data):
            for col_index, col_item in enumerate(row_item):
                self.ws.cell(row=row_index + 1, column=col_index + 1, value=col_item)

        self.ws.save(self.filepath)
        logger.info("write to excel OVER !")


if __name__ == '__main__':
    ts = ExcelOp('./a.xlsx')
    ts.get_row_clo_num()
    ts.get_row_col_value()
    ts.write_excel_value(6, 1, '6-1')
    ts.write_excel_value(6, 2, '6-2')
